#!/usr/bin/env python3
"""
MySQL数据库构建脚本
自动化创建数据库、表结构，并导入数据
"""

import sys
import os
import time
from datetime import datetime
import configparser
from pathlib import Path

# 添加项目根目录到Python路径
project_root = Path(__file__).parent.parent
sys.path.append(str(project_root))

try:
    import pymysql
    from simulator.enhanced_simulator import EnhancedMobileDataSimulator
    from simulator.enhanced_models import DatabaseConfig, UserRecordV2
except ImportError as e:
    print(f"❌ 导入依赖失败: {e}")
    print("请确保已安装: pip install pymysql pandas numpy pydantic")
    sys.exit(1)


class MySQLBuilder:
    """MySQL数据库构建器"""
    
    def __init__(self, host='localhost', port=3306, user='root', password='', config_file=None):
        """
        初始化MySQL构建器
        
        Args:
            host: MySQL主机地址
            port: MySQL端口
            user: MySQL用户名
            password: MySQL密码
            config_file: 配置文件路径
        """
        # 加载配置文件
        self.config = self._load_config(config_file)

        # self.host = host
        # self.port = port
        # self.user = user
        # self.password = password
        
        self.host = self.config.get('mysql', 'host', fallback='localhost')
        self.port = int(self.config.get('mysql', 'port', fallback=3306))
        self.user = self.config.get('mysql', 'user', fallback='root')
        self.password = self.config.get('mysql', 'password', fallback='')
        
        
        # 从配置文件读取数据库名称，如果没有则使用默认值
        self.database = self.config.get('mysql', 'database', fallback='mobile_data_sim')
        
        # 从配置文件读取表名配置
        self.main_table = self.config.get('data_generation', 'main_table', fallback='user_records')
        self.backup_table = self.config.get('data_generation', 'backup_table', fallback='user_records_backup')
        self.temp_table = self.config.get('data_generation', 'temp_table', fallback='user_records_temp')

        self.users = int(self.config.get('data_generation', 'default_users', fallback=1000))
        # 支持days和months两种配置
        if self.config.has_option('data_generation', 'default_days'):
            self.days = int(self.config.get('data_generation', 'default_days', fallback=30))
            self.months = None
        else:
            self.days = None
            self.months = int(self.config.get('data_generation', 'default_months', fallback=6))
        self.start_date = datetime.strptime(self.config.get('data_generation', 'start_date', fallback='2025-07-30'), '%Y-%m-%d')
    
    def _load_config(self, config_file):
        """加载配置文件"""
        if config_file is None:
            config_file = Path(__file__).parent / 'mysql_config.ini'
        elif isinstance(config_file, str):
            config_file = Path(config_file)
        
        config = configparser.ConfigParser()
        
        if config_file.exists():
            config.read(config_file)
            print(f"✅ 已加载配置文件: {config_file}")
        else:
            print(f"❌ 配置文件不存在: {config_file}，程序终止")
            sys.exit(1)

        print(str(config.get('data_generation', 'main_table')))    
        
        return config
    
    def test_connection(self):
        """测试MySQL连接"""
        try:
            connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                charset='utf8mb4'
            )
            
            cursor = connection.cursor()
            cursor.execute("SELECT VERSION()")
            version = cursor.fetchone()[0]
            
            connection.close()
            print(f"✅ MySQL连接成功")
            print(f"   版本: {version}")
            print(f"   主机: {self.host}:{self.port}")
            print(f"   用户: {self.user}")
            return True
            
        except Exception as e:
            print(f"❌ MySQL连接失败: {e}")
            return False
    
    def create_database(self):
        """创建数据库"""
        try:
            connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                charset='utf8mb4'
            )
            
            cursor = connection.cursor()
            
            # 创建数据库
            cursor.execute(f"""
                CREATE DATABASE IF NOT EXISTS {self.database} 
                CHARACTER SET utf8mb4 
                COLLATE utf8mb4_unicode_ci
            """)
            
            print(f"✅ 数据库 '{self.database}' 创建成功")
            
            connection.close()
            return True
            
        except Exception as e:
            print(f"❌ 创建数据库失败: {e}")
            return False
    
    def execute_sql_file(self, sql_file_path):
        """执行SQL文件"""
        try:
            # 读取SQL文件
            with open(sql_file_path, 'r', encoding='utf-8') as f:
                sql_content = f.read()
            
            connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database,
                charset='utf8mb4'
            )
            
            cursor = connection.cursor()
            
            # 分割SQL语句并执行
            sql_statements = [stmt.strip() for stmt in sql_content.split(';') if stmt.strip()]
            
            for i, statement in enumerate(sql_statements):
                if statement.upper().startswith(('CREATE', 'DROP', 'INSERT', 'UPDATE', 'DELETE', 'ALTER')):
                    try:
                        cursor.execute(statement)
                        print(f"✅ 执行SQL语句 {i+1}/{len(sql_statements)}")
                    except Exception as e:
                        print(f"⚠️  SQL语句执行警告 {i+1}: {e}")
            
            connection.commit()
            connection.close()
            
            print(f"✅ SQL文件执行完成: {sql_file_path}")
            return True
            
        except Exception as e:
            print(f"❌ 执行SQL文件失败: {e}")
            return False
    
    def create_table_structure(self):
        """创建表结构"""
        sql_file = project_root / 'scripts' / 'mysql_setup.sql'
        
        if not sql_file.exists():
            print(f"❌ SQL文件不存在: {sql_file}")
            return False
        
        print(f"📄 执行SQL脚本: {sql_file}")
        return self.execute_sql_file(sql_file)
    
    def import_sample_data(self):
        """导入示例数据"""
        print(f"📊 开始生成示例数据...")
        print(f"   用户数量: {self.users}")
        if self.days is not None:
            print(f"   天数数量: {self.days}")
            num_users = self.users
            num_days = self.days
            num_months = None
        else:
            print(f"   月份数量: {self.months}")
            num_users = self.users
            num_months = self.months
            num_days = None
        
        try:
            # 创建模拟器配置
            config = EnhancedMobileDataSimulator.create_default_config()
            config.num_users = num_users
            if num_days is not None:
                config.num_days = num_days
                config.num_months = None
            else:
                config.num_months = num_months
                config.num_days = None
            config.start_date = self.start_date
            config.random_seed = 42
            
            # 创建数据库配置
            db_config = DatabaseConfig(
                db_type="mysql",
                host=self.host,
                port=self.port,
                username=self.user,
                password=self.password,
                database=self.database,
                table_name=self.main_table
            )
            
            # 创建模拟器
            simulator = EnhancedMobileDataSimulator(config)
            
            # 生成数据
            start_time = time.time()
            df = simulator.generate_all_data()
            generation_time = time.time() - start_time
            
            print(f"✅ 数据生成完成，耗时: {generation_time:.2f}秒")
            print(f"   记录数: {len(df):,}")
            
            # 保存到MySQL
            start_time = time.time()
            simulator.save_to_mysql(df, db_config)
            import_time = time.time() - start_time
            
            print(f"✅ 数据导入完成，耗时: {import_time:.2f}秒")
            
            # 验证数据
            self.verify_data()
            
            return True
            
        except Exception as e:
            print(f"❌ 导入示例数据失败: {e}")
            import traceback
            print("详细错误信息:")
            traceback.print_exc()
            return False
    
    def verify_data(self):
        """验证数据"""
        try:
            connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database,
                charset='utf8mb4'
            )
            
            cursor = connection.cursor()
            
            # 检查表是否存在
            cursor.execute("SHOW TABLES")
            tables = [table[0] for table in cursor.fetchall()]
            print(f"📋 数据库表: {', '.join(tables)}")
            
            # 检查记录数
            cursor.execute(f"SELECT COUNT(*) FROM {self.main_table}")
            total_records = cursor.fetchone()[0]
            print(f"📊 总记录数: {total_records:,}")
            
            # 检查异网用户分布
            cursor.execute(f"""
                SELECT 
                    LABEL_IS_DIFF,
                    COUNT(*) as count,
                    ROUND(COUNT(*) * 100.0 / %s, 1) as percentage
                FROM {self.main_table} 
                GROUP BY LABEL_IS_DIFF
            """, (total_records,))
            
            print(f"📈 异网用户分布:")
            for row in cursor.fetchall():
                is_diff, count, percentage = row
                user_type = "异网用户" if is_diff == 1 else "正常用户"
                print(f"   {user_type}: {count:,} ({percentage}%)")
            
            # 检查性别分布
            cursor.execute(f"""
                SELECT 
                    COALESCE(ATTR_GENDER, '未知') as gender,
                    COUNT(*) as count
                FROM {self.main_table} 
                WHERE ATTR_GENDER IS NOT NULL
                GROUP BY ATTR_GENDER
            """)
            
            print(f"👥 性别分布:")
            for row in cursor.fetchall():
                gender, count = row
                print(f"   {gender}: {count:,}")
            
            # 检查区域分布（前5名）
            cursor.execute(f"""
                SELECT ATTR_CITY, COUNT(*) as count 
                FROM {self.main_table} 
                GROUP BY ATTR_CITY 
                ORDER BY count DESC 
                LIMIT 5
            """)
            
            print(f"🌍 区域分布（前5名）:")
            for row in cursor.fetchall():
                city, count = row
                print(f"   {city}: {count:,}")
            
            # 检查V2版本特有字段
            print(f"📊 V2版本数据验证:")
            
            # 检查套餐信息
            cursor.execute(f"""
                SELECT 
                    COUNT(*) as total,
                    COUNT(ATTR_NUMBER_PLAN_NAME) as has_plan_name,
                    COUNT(METRIC_NUMBER_DATA_ALLOWANCE_MB) as has_data_allowance
                FROM {self.main_table} 
                WHERE LABEL_IS_DIFF = 0
            """)
            
            row = cursor.fetchone()
            if row:
                total, has_plan_name, has_data_allowance = row
                print(f"   套餐信息完整性: {has_plan_name}/{total} ({has_plan_name/total*100:.1f}%)")
                print(f"   流量套餐信息: {has_data_allowance}/{total} ({has_data_allowance/total*100:.1f}%)")
            
            # 检查基站使用统计
            cursor.execute(f"""
                SELECT 
                    COUNT(METRIC_LAC_DAY_TOTAL_TIME_MINUTES) as has_day_stats,
                    COUNT(METRIC_LAC_NIGHT_TOTAL_TIME_MINUTES) as has_night_stats
                FROM {self.main_table} 
                WHERE LABEL_IS_DIFF = 0
            """)
            
            row = cursor.fetchone()
            if row:
                has_day_stats, has_night_stats = row
                print(f"   基站使用统计: 日间{has_day_stats}条, 夜间{has_night_stats}条")
            
            # 检查信用分分布
            cursor.execute(f"""
                SELECT 
                    MIN(ATTR_CREDIT_SCORE) as min_score,
                    MAX(ATTR_CREDIT_SCORE) as max_score,
                    AVG(ATTR_CREDIT_SCORE) as avg_score
                FROM {self.main_table} 
                WHERE ATTR_CREDIT_SCORE IS NOT NULL AND LABEL_IS_DIFF = 0
            """)
            
            row = cursor.fetchone()
            if row:
                min_score, max_score, avg_score = row
                print(f"   信用分范围: {min_score}-{max_score}, 平均: {avg_score:.1f}")
            
            connection.close()
            return True
            
        except Exception as e:
            print(f"❌ 数据验证失败: {e}")
            return False
    
    def show_usage_examples(self):
        """显示使用示例"""
        print(f"\n" + "="*60)
        print("📖 MySQL数据库使用示例")
        print("="*60)
        
        print(f"🔌 连接数据库:")
        print(f"mysql -h {self.host} -P {self.port} -u {self.user} -p {self.database}")
        
        print(f"\n📊 常用查询:")
        queries = [
            ("查看总记录数", f"SELECT COUNT(*) FROM {self.main_table};"),
            ("异网用户统计", f"SELECT LABEL_IS_DIFF, COUNT(*) FROM {self.main_table} GROUP BY LABEL_IS_DIFF;"),
            ("性别分布", f"SELECT ATTR_GENDER, COUNT(*) FROM {self.main_table} WHERE ATTR_GENDER IS NOT NULL GROUP BY ATTR_GENDER;"),
            ("区域分布", f"SELECT ATTR_CITY, COUNT(*) FROM {self.main_table} GROUP BY ATTR_CITY ORDER BY COUNT(*) DESC LIMIT 10;"),
            ("平均消费", f"SELECT AVG(METRIC_MONTHLY_CONSUMPTION) FROM {self.main_table} WHERE LABEL_IS_DIFF = 0;"),
            ("套餐分布", f"SELECT ATTR_NUMBER_PLAN_NAME, COUNT(*) FROM {self.main_table} WHERE ATTR_NUMBER_PLAN_NAME IS NOT NULL GROUP BY ATTR_NUMBER_PLAN_NAME;"),
            ("信用分分布", f"SELECT FLOOR(ATTR_CREDIT_SCORE/50)*50 as score_range, COUNT(*) FROM {self.main_table} WHERE ATTR_CREDIT_SCORE IS NOT NULL GROUP BY FLOOR(ATTR_CREDIT_SCORE/50)*50 ORDER BY score_range;"),
            ("基站使用统计", f"SELECT AVG(METRIC_LAC_DAY_TOTAL_TIME_MINUTES) as avg_day_time, AVG(METRIC_LAC_NIGHT_TOTAL_TIME_MINUTES) as avg_night_time FROM {self.main_table} WHERE LABEL_IS_DIFF = 0;"),
            ("使用视图", "SELECT * FROM v_user_summary;"),
            ("调用存储过程", "CALL sp_data_statistics('20250731');")
        ]
        
        for desc, query in queries:
            print(f"\n📌 {desc}:")
            print(f"   {query}")
        
        print(f"\n🐍 Python连接示例:")
        print(f"""
import pymysql
import pandas as pd

# 连接数据库
connection = pymysql.connect(
    host='{self.host}',
    port={self.port},
    user='{self.user}',
    password='{self.password}',
    database='{self.database}',  # 从配置文件读取的数据库名称
    charset='utf8mb4'
)

# 读取数据
df = pd.read_sql(f'SELECT * FROM {self.main_table} LIMIT 1000', connection)
print(f'读取数据: {{len(df)}} 条记录')

connection.close()
""")


def main():
    """主函数"""
    print("🚀 MySQL数据库构建脚本")
    print("="*60)
    
    # 解析命令行参数
    import argparse
    parser = argparse.ArgumentParser(description='MySQL数据库构建工具')
    parser.add_argument('--host', default='localhost', help='MySQL主机地址')
    parser.add_argument('--port', type=int, default=3306, help='MySQL端口')
    parser.add_argument('--user', default='root', help='MySQL用户名')
    parser.add_argument('--password', default='', help='MySQL密码')
    parser.add_argument('--config', default=None, help='MySQL配置文件路径')
    parser.add_argument('--users', type=int, help='生成用户数量')
    parser.add_argument('--days', type=int, default=None, help='生成天数数量')
    parser.add_argument('--months', type=int, default=None, help='生成月份数量')
    parser.add_argument('--no-data', action='store_true', help='只创建表结构，不导入数据')
    
    args = parser.parse_args()
    
    # 创建构建器
    builder = MySQLBuilder(
        host=args.host,
        port=args.port,
        user=args.user,
        password=args.password,
        config_file=args.config
    )
    
    # 如果命令行指定了参数，覆盖配置文件
    if args.users is not None:
        builder.users = args.users
    if args.days is not None:
        builder.days = args.days
        builder.months = None
    elif args.months is not None:
        builder.months = args.months
        builder.days = None

    try:
        # 1. 测试连接
        if not builder.test_connection():
            print("\n❌ 请检查MySQL服务和连接参数")
            return False
        
        # 2. 创建数据库
        print(f"\n📦 创建数据库 '{builder.database}'...")
        if not builder.create_database():
            return False
        
        # 3. 创建表结构
        print(f"\n🏗️  创建表结构...")
        if not builder.create_table_structure():
            return False
        
        # 4. 导入示例数据（如果需要）
        if not args.no_data:
            print(f"\n📥 导入示例数据...")
            if not builder.import_sample_data():
                return False
        else:
            print(f"\n⏭️  跳过数据导入")
            builder.verify_data()
        
        # 5. 显示使用示例
        builder.show_usage_examples()
        
        print(f"\n🎉 MySQL数据库构建完成！")
        return True
        
    except KeyboardInterrupt:
        print(f"\n\n⚠️  用户取消操作")
        return False
    except Exception as e:
        print(f"\n❌ 构建失败: {e}")
        import traceback
        traceback.print_exc()
        return False


if __name__ == "__main__":
    success = main()
    sys.exit(0 if success else 1)
